IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetLog4NetEntries]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetLog4NetEntries]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetLog4NetEntries]
	@MinDate datetime = '1/1/1900',
	@MaxDate datetime = '12/31/9999',
	@Level varchar(255) = '',
	@Logger varchar(255) = '',
	@Message varchar(255) = '',
	@Exception varchar(255) = '',
	@sortColumn VARCHAR(50),
	@sortAscending bit,
	@pageNumber int, -- 1 based
	@rowsToFetch int,
	@totalRowCount int
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @startRowIndex int
	SET @startRowIndex = 1 + (@pageNumber - 1) * @rowsToFetch

	WITH Details as(
	SELECT ROW_NUMBER() OVER (ORDER BY 
	-- varchar
	CASE WHEN @sortAscending = 1 THEN 
		CASE @sortColumn WHEN 'Level' THEN [Level]
						 WHEN 'Logger' THEN Logger
						 WHEN 'Message' THEN [Message]
						 WHEN 'Exception' THEN Exception
						 ELSE NULL END
		ELSE NULL
	END,
	CASE WHEN @sortAscending = 0 THEN
		CASE @sortColumn WHEN 'Level' THEN [Level]
						 WHEN 'Logger' THEN Logger
						 WHEN 'Message' THEN [Message]
						 WHEN 'Exception' THEN Exception
						 ELSE NULL END
		ELSE NULL
	END DESC,
	-- Datetime
	CASE WHEN @sortAscending = 1 THEN
		CASE @sortColumn WHEN 'Date' THEN Date
						 ELSE NULL END
		ELSE NULL
	END,
	CASE WHEN @sortAscending = 0 THEN
		CASE @sortColumn WHEN 'Date' THEN Date
						 ELSE NULL END
		ELSE NULL
	END DESC,
	-- int
	CASE WHEN @sortAscending = 1 THEN
		CASE @sortColumn WHEN 'Id' THEN Id
						 ELSE NULL END
		ELSE NULL
	END,
	CASE WHEN @sortAscending = 0 THEN
		CASE @sortColumn WHEN 'Id' THEN Id
						 ELSE NULL END
		ELSE NULL
	END DESC
) as RowNumber,
	Id, Date, Thread, [Level], Logger, LEFT([Message], 50) [Message], LEFT(Exception, 50) Exception 
	FROM dbo.Log4Net
	WHERE Date >= @MinDate AND Date <= @MaxDate
	AND Logger LIKE @Logger + '%'
	AND [Level] LIKE @Level + '%' 
	AND [Message] LIKE @Message + '%'
	AND Exception Like @Exception + '%')

	SELECT Id, Date, Thread, [Level], Logger, [Message], Exception FROM Details
	WHERE RowNumber BETWEEN @startRowIndex AND @startRowIndex + @rowsToFetch - 1
	OR 0 = @rowsToFetch

	IF @totalRowCount = 0
    BEGIN
		SELECT COUNT(Id) RecordCount
		FROM dbo.Log4Net
		WHERE Date >= @MinDate AND Date <= @MaxDate
		AND Logger LIKE @Logger + '%'
		AND [Level] LIKE @Level + '%'
		AND [Message] LIKE @Message + '%'
		AND Exception Like @Exception + '%'
	END
END

GO